package com.ylfstu.jdbc.datasource;


import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.jupiter.api.Test;

import javax.management.Query;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: Joy_yang
 * @Date: 2022/10/20 17:41
 */
public class DBUtils_USE {

    //使用Apache-DBUtils 工具类 + druid 完成对表的crud操作
    @Test
    public void testQueryMany() throws SQLException {

        //1.得到连接(druid)
        Connection connection = JdbcUtilsByDruid.getConnection();
        //2.使用 DBUtils 类和接口，先引入 DBUtils 相关的jar，加入到本project
        //3.创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //4.执行相关方法，返回ArrayList 结果集
        String sql = "select * from actor where id >= ?";

        List<Actor> query = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);

        System.out.println("输出集合的信息：");
        for (Actor actor : query) {
            System.out.println(actor);
        }

        //释放资源
        JdbcUtilsByDruid.close(null, null, connection);

    }

    //演示 apache-dbutils + druid 完成 返回的结果是单行记录(单个对象)
    @Test
    public void testQuerySingle() throws SQLException {

        Connection connection = JdbcUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id = ?";
        //返回单行记录-->单个对象，使用的Handler 是 BeanHandler
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 3);
        System.out.println(actor);
        JdbcUtilsByDruid.close(null, null, connection);

    }

    //演示 apache-dbutils + druid 完成 查询结果是单行单列(即单个值) 返回的就是Object
    @Test
    public void testScalar() throws SQLException {
        Connection connection = JdbcUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select name from actor where id = ?";
        //返回单行单列-->单个标量，使用的Handler 是 ScalarHandler
        Object obj = queryRunner.query(connection, sql, new ScalarHandler(), 3);
        System.out.println(obj);
        JdbcUtilsByDruid.close(null, null, connection);
    }

    //演示 apache-dbutils + druid 完成 dml(update,delete,insert)
    @Test
    public void testDML() throws SQLException {
        Connection connection = JdbcUtilsByDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
//        String sql = "insert into actor values(null, ?, ?, ?, ?)";
        String sql = "delete from actor where id = ?";
        //1. 执行dml 操作是 queryRunner.update
        //2. 返回的值是受影响的行数，
//        int affectedRow = queryRunner.update(connection, sql, "林青霞", "女", "1977-01-01", "116");
        int affectedRow = queryRunner.update(connection, sql, 4);
        System.out.println(affectedRow > 0 ? "执行成功" : "执行失败");
        JdbcUtilsByDruid.close(null, null, connection);
    }
}











